﻿using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.CodeAnalysis;
using System.Threading.Tasks;

namespace PMP.Data
{
    public class DB : DbContext
    {

        public DataTable Query(string sql, List<SqlParameter> list = null)
        {
            using (var conn = base.Database.GetDbConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                if (list != null)
                {
                    cmd.Parameters.AddRange(list.ToArray());
                }
                var reader = cmd.ExecuteReader();
                var dt = new DataTable();
                dt.Load(reader);
                reader.Close();
                conn.Close();
                return dt;
            }
        }

        public object Single(string sql, List<SqlParameter> list = null)
        {
            using (var conn = base.Database.GetDbConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                if (list != null)
                {
                    cmd.Parameters.AddRange(list.ToArray());
                }
                var obj = cmd.ExecuteScalar();
                conn.Close();
                return obj;
            }
        }


        public async Task<DataTable> QueryAsync(string sql, List<SqlParameter> list = null)
        {
            using (var conn = base.Database.GetDbConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                if (list != null)
                {
                    cmd.Parameters.AddRange(list.ToArray());
                }
                var reader = await cmd.ExecuteReaderAsync();
                var dt = new DataTable();
                dt.Load(reader);
                reader.Close();
                conn.Close();
                return dt;
            }
        }

        public async Task<object> SingleAsync(string sql, List<SqlParameter> list = null)
        {
            using (var conn = base.Database.GetDbConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                if (list != null)
                {
                    cmd.Parameters.AddRange(list.ToArray());
                }
                var obj = await cmd.ExecuteScalarAsync();
                conn.Close();
                return obj;
            }
        }

        private List<T> GetList<T>(DataTable dt) where T : new()
        {
            List<T> modelList = new List<T>();
            if (dt == null || dt.Rows.Count == 0)
            {
                return modelList;
            }
            foreach (DataRow dr in dt.Rows)
            {
                T model = new T();
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    System.Reflection.PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                        propertyInfo.SetValue(model, dr[i], null);
                }

                modelList.Add(model);
            }
            return modelList;
        }

        public List<T> QueryMode<T>(string sql, List<SqlParameter> list = null) where T : new()
        {
            DataTable dt = Query(sql, list);
            return GetList<T>(dt);
        }
        public async Task<List<T>> QueryModeAsync<T>(string sql, List<SqlParameter> list = null) where T : new()
        {
            DataTable dt = await QueryAsync(sql, list);
            return GetList<T>(dt);
        }
    }
}
